package com.housekeeping.housekeeping.cleanerInformation.dao.impl;

import com.housekeeping.housekeeping.cleanerInformation.dao.ICleanerRestoreDao;
import com.housekeeping.housekeeping.cleanerInformation.entity.Cleaner;
import com.housekeeping.housekeeping.cleanerInformation.utils.DbManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hua
 * @version JDK1.8
 * @date 2021/1/24 21:23
 */
public class CleanerRestoreDaoImpl implements ICleanerRestoreDao {
    /**
     * 分页查询
     * @param limit 第几页
     * @param size 分页大小
     * @return
     * @throws SQLException
     */
    @Override
    public List<Cleaner> selectRestoreByPage(int limit, int size) throws SQLException {
        //存放从数据库中查到的数据集合
        ArrayList<Cleaner> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        try {
            //获取连接对象
            connection = DbManager.getConnection();
            //mysql 语句
            String mysql = "SELECT * FROM cleaner_information WHERE is_delete = 0 LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,limit);
            ps.setObject(2,size);
            //得到结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        }  finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }

    /**
     * 查询数据总条数
     * @return
     * @throws SQLException
     */
    @Override
    public int selectRestoreCount() throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        int i = 0;
        try{
            connection = DbManager.getConnection();
            String mysql = "SELECT count(*) FROM cleaner_information WHERE is_delete = 0";
            ps = connection.prepareStatement(mysql);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                i = resultSet.getInt(1);
            }
        }finally {
            DbManager.closeAll(resultSet,connection,ps);
        }
        return i;
    }

    /**
     * 删除某个保洁员 根据id删除
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteRestore(int id) throws SQLException {
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作后返回的受影响行数
        int i = 0;

        try {
            connection = DbManager.getConnection();
            String mysql = "DELETE FROM cleaner_information WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            ps.setObject(1,(int)id);
            i = ps.executeUpdate();
        }finally {
            //关流
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 根据id删除选中的保洁员信息
     * @param list 存放id的集合
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteRestoreAll(List<Cleaner> list) throws SQLException {
        //声明数据库连对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作受影响行数
        int i = 0;
        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "DELETE FROM cleaner_information WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            for (Cleaner c:list) {
                ps.setObject(1,c.getId());
                i = ps.executeUpdate();
            }

        }finally {
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 根据id还原某个保洁员
     * @param id 保洁员对象
     * @return
     * @throws SQLException
     */
    @Override
    public int restore(int id) throws SQLException {
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作后返回的受影响行数
        int i = 0;

        try {
            connection = DbManager.getConnection();
            String mysql = "UPDATE cleaner_information SET is_delete = 1 WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            ps.setObject(1,(int)id);
            i = ps.executeUpdate();
        }finally {
            //关流
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 根据id批量还原保洁员
     * @param list 存放id的集合
     * @return
     * @throws SQLException
     */
    @Override
    public int restoreAll(List<Cleaner> list) throws SQLException {
        //声明数据库连对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //数据库操作受影响行数
        int i = 0;
        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "UPDATE cleaner_information SET is_delete = 1 WHERE id = ?";
            ps = connection.prepareStatement(mysql);
            for (Cleaner c:list) {
                ps.setObject(1,c.getId());
                i = ps.executeUpdate();
            }

        }finally {
            DbManager.closeAll(connection,ps);
        }

        return i;
    }

    /**
     * 模糊查询功能, 根据姓名查询
     * @param limit
     * @param size
     * @param name
     * @return
     * @throws SQLException
     */
    @Override
    public List<Cleaner> vagueSelectRestoreName(int limit, int size, String name) throws SQLException {
        ArrayList<Cleaner> list = new ArrayList<>();
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //声明结果集
        ResultSet resultSet = null;

        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "select *from cleaner_information where is_delete = 0 AND name like "+"'"+"%" + name + "%"+"' LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,limit);
            ps.setObject(2,size);
            //获取查询结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        }finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }

    /**
     * 精确查询功能, 根据电话号码精确查询
     * @param limit
     * @param size
     * @param telephoneNumber
     * @return
     * @throws SQLException
     */
    @Override
    public List<Cleaner> selectRestoreTelephoneNumber(int limit, int size, String telephoneNumber) throws SQLException {
        ArrayList<Cleaner> list = new ArrayList<>();
        //声明数据库连接对象
        Connection connection = null;
        //声明数据库连接流
        PreparedStatement ps = null;
        //声明结果集
        ResultSet resultSet = null;

        try {
            //获取数据库连接对象
            connection = DbManager.getConnection();
            //mysql语句
            String mysql = "select *from cleaner_information where is_delete = 0 AND telephone_number = ? LIMIT ?,?";
            //执行mysql语句
            ps = connection.prepareStatement(mysql);

            ps.setObject(1,telephoneNumber);
            ps.setObject(2,limit);
            ps.setObject(3,size);
            //获取查询结果集
            resultSet = ps.executeQuery();
            //遍历结果集
            while (resultSet.next()){
                Cleaner cleaner = new Cleaner();
                cleaner.setId(resultSet.getInt("id"));
                cleaner.setPortrait(resultSet.getString("head_portrait"));
                cleaner.setName(resultSet.getString("name"));
                cleaner.setSex(resultSet.getString("sex"));
                cleaner.setAge(resultSet.getInt("age"));
                cleaner.setTelephoneNumber(resultSet.getString("telephone_number"));
                cleaner.setSynopsis(resultSet.getString("synopsis"));
                list.add(cleaner);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关流
            DbManager.closeAll(resultSet,connection,ps);
        }
        return list;
    }
}
